Lesson 3 Tidy data
… in which we explore the concept of Tidy Data and learn more advanced data wrangling techniques
Once again, we start by loading the tidyverse. In the lecture video you can also see a recap, a speed run of sorts, of lectures 1 and 2.
3.1 Tidy data
Let’s get started with this pivotal topic.
3.1.1 What and why is tidy data?
There is one concept which also lends it’s name to the tidyverse that I want to talk about. Tidy Data is a way of turning your datasets into a uniform shape. This makes it easier to develop and work with tools because we get a consistent interface. Once you know how to turn any dataset into a tidy dataset, you are on home turf and can express your ideas more fluently in code. Getting there can sometimes be tricky, but I will give you the most important tools.
In tidy data, each variable (feature) forms it’s own column. Each observation forms a row. And each cell is a single value (measurement). Furthermore, information about the same things belongs in one table.

Figure from https://r4ds.had.co.nz/tidy-data.html Wickham and Grolemund10
3.1.2 Make data tidy
with the tidyr package.
“Happy families are all alike; every unhappy family is unhappy in its own way”
— Leo Tolstoy (https://tidyr.tidyverse.org/articles/tidy-data.html)
And this quote holds true for messy datasets as well.
The tidyr package contained in the tidyverse provides small example datasets to
demonstrate what this means in practice.
Hadley Wickham and Garrett Grolemund use these in their book as well (https://r4ds.had.co.nz/tidy-data.html).11
Let’s make some data tidy!
table1, table2, table3, table4a, table4b,
and table5 all display the number of TB cases documented by
the World Health Organization in Afghanistan, Brazil,
and China between 1999 and 2000.
The first of these is in the tidy format, the others are not:
table1This nicely qualifies as tidy data. Every row is uniquely identified by the country and year, and all other columns are properties of the specific country in this specific year.
3.1.3 pivot_wider
Now it gets interesting.
table2 still looks organized, but it is not tidy (by our definition).
Note, this doesn’t say the format is useless — it has it’s places —
but it will not fit in as snugly with our tools.
The column type is not a feature of the country,
rather the actual features are hidden in that column with
their values in the count column.
table2In order to make it tidy, this dataset would needs become wider.
table2 %>%
pivot_wider(names_from = type, values_from = count)
3.1.4 separate
In table3, two features are jammed into one column.
This is annoying, because we can’t easily calculate
with the values; they are stored as text and
separated by a slash like cases/population.
table3Ideally, we would want to separate this column into two:
3.1.5 pivot_longer
table4a and table4b split the data into two different tables,
which again makes it harder to calculate with.
This data is so closely related, we would want it in one table.
And another principle of tidy data is violated.
Notice the column names?
1999 is not a feature that Afghanistan can have.
Rather, it is the value for a feature (namely the year),
while the values in the 1999 column are in fact
values for the feature population (in table4a)
and cases (in table4b).
table4a
table4b
table4a %>%
pivot_longer(-country, names_to = "year", values_to = "cases")
table4b %>%
pivot_longer(-country, names_to = "year", values_to = "population")We have another case where we are doing a very similar thing twice. There is a general rule of thumb that says:
“If you copy and paste the same code 3 times, you should probably write a function.”
This no only has the advantage of reducing code duplication and enabling us to potentially reuse our code later in another project, it also aids readability because we are forced to give this stop a name.
clean_wide_data <- function(data, values_column) {
data %>%
pivot_longer(-country, names_to = "year", values_to = values_column)
}We can then use this function for both tables.
clean4a <- table4a %>%
clean_wide_data("cases")
clean4b <- table4b %>%
clean_wide_data("population")
3.1.6 left_join
Now is the time to join clean4a and clean4b together.
For this, we need an operation known from databases
as a join.
In fact, this whole concept of
tidy data is closely related to databases and
something called Codd`s normal forms12
so I am throwing these references in here just in case you are interested in
the theoretical foundations.
But without further ado:
3.1.7 unite
In table5, we have the same problem as in table3 and
additionally the opposite problem!
This time, feature that should be one column (namely year)
is spread across two columns (century and year).
table5What we want to do is unite those into one,
and also deal with the other problem.
However, we when do this we find out the our newly
created year, cases and population columns
are actually stored as text, not numbers!
So in the next step, we convert those into numbers
with the parse_number function.
table5 %>%
unite("year", century, year, sep = "") %>%
separate(rate, c("cases", "population")) %>%
mutate(
year = parse_number(year),
cases = parse_number(cases),
population = parse_number(population)
)parse_number is a bit like a less strict version of as.numeric.
While as.numeric can only deal with text that contains only a number
and nothing else, parse_number can help us by extracting numbers
even if there is some non-number text around them:
as.numeric("we have 42 sheep")[1] NA
parse_number handles this, no questions asked:
parse_number("we have 42 sheep")[1] 42
Notice, how we applied the same function parse_number to
multiple columns of our data?
If we notice such a pattern, where there is lot’s of code repetition,
chances are that there is a more elegant solution.
You don’t have to find that elegant solution at first try,
but keeping an open mind will improve your code in the long run.
In this case, let me tell you about the across function.
We can use it inside of dplyr verbs such as mutate and summarise
to apply a function to multiple columns:
table5 %>%
unite("year", century, year, sep = "") %>%
separate(rate, c("cases", "population")) %>%
mutate(
across(c(year, cases, population), parse_number)
)As it’s first argument it takes a vector of column names
(the c(...) bit) or a tidy-select specification (see ?dplyr_tidy_select)
and as it’s second argument either one function or even a list of functions (with names).
Another way of specifying what columns to use here
would be to say “every column but the country”
with -country.
table5 %>%
unite("year", century, year, sep = "") %>%
separate(rate, c("cases", "population")) %>%
mutate(
across(-country, parse_number)
)3.1.8 Another example
Let us look at one last example of data that needs tidying,
which is also provided by the tidyr package as an example:
head(billboard)This is a lot of columns! For 76 weeks after a song entered the top 100 (I assume in the USA) its position is recorded. It might be in this format because it made data entry easier, or the previous person wanted to make plots in excel, where this wide format is used to denote multiple traces. In any event, for our style of visualizations with the grammar of graphics, we want a column to represent a feature, so this data needs to get longer:
billboard %>%
pivot_longer(starts_with("wk"), names_to = "week", values_to = "placement") %>%
mutate(week = parse_number(week)) %>%
head()Let’s save this to a variable.
And while we are at it, we can save the extra mutate-step
by performing the transformation from text to numbers
right inside of the pivot_longer function.
tidy_bilboard <- billboard %>%
pivot_longer(starts_with("wk"),
names_to = "week",
values_to = "placement",
names_prefix = "wk",
names_transform = list(week = as.integer)
)
tidy_bilboard %>% head(10)Yes, those pivot functions are really powerful!
A notable difference that often happens between long- and wide-format data is the way missing data is handled.
Because every row needs to have the same number of columns,
and in the wide format every column is a week,
there are bound to be a lot of NA values wherever
a song was simply no longer in the top 100 at the
specified week.
Those missing values are then very explicit.
In the long format we have the option to make the missing values
implicit by simply omitting the row where
there is no meaningful information.
With the function na.omitt for example, we can remove
all rows that have NA somewhere:
Let’s reward ourselves with a little visualization.
Here, I am also introducing the plotly package,
which has the handy function ggplotly to turn a
regular ggplot into an interactive plot.
Plotly also has its own way of building plots,
which you might want to check out for advanced interactive
or 3-dimensional plots: https://plotly.com/r/,
but for the most part we don’t need to worry about it due to the amazingly
simple ggplotly translation function.
plt <- tidy_bilboard %>%
ggplot(aes(week, placement)) +
geom_point(aes(label = paste(artist, track))) +
geom_line(aes(group = paste(artist, track)))
plotly::ggplotly(plt)This whole tidy data idea might seem like just another way of moving numbers around. But once you build the mental model for it, it will truly transform the way you are able to think about data. Both for data wrangling with dplyr, as shown last week, and also for data visualization with ggplot, a journey we began in the first week and that is still well underway.
3.2 More shapes for data
Data comes in many shapes and R has more than just
vectors and dataframes / tibbles.
I this course we are omitting matrices,
which store data of the same type in 2 dimensions,
and it’s multi-dimensional equivalent arrays.
What we are not omitting, and in fact have already teased
but never properly defined is lists.
3.2.1 Lists
On first glance, lists are very similar to atomic vectors, as they are both one dimensional data structures and both of them can have names.
c(first = 1, second = 2) first second
1 2
list(first = 1, second = 2)$first
[1] 1
$second
[1] 2
What sets them apart is that while atomic vectors can only contain data of the same type (like only numbers or only text), a list can contain anything, even other lists!
$first
[1] 1
$second
[1] 2
[[3]]
[1] "some text"
[[4]]
[[4]][[1]]
[1] 1
[[4]][[2]]
[1] 2
[[5]]
[1] 1 2 3 4 5
As it turns out, dataframes internally are also lists, namely a list of columns. They just have some more properties (which R calls attributes) that tell R to display it in the familiar rectangular shape.
3.2.2 Nested data
The tidyr package provides more tools for dealing
with data in various shapes.
We just discovered the first set of operations called pivots
and joins to get a feel for tidy data and to obtain it from various formats.
But data is not always rectangular like we can show it in a spreadsheet.
Sometimes data already comes in a nested
form, sometimes we create nested data because it serves
our purpose.
So, what do I mean by nested?
Remember that lists can contain elements of any type, even other lists.
If we have a list that contains more lists, we call it nested e.g.
But nested list are not always fun to work with, and when there is a straightforward way to represent the same data in a rectangular, flat format, we most likely want to do that. We will deal with data rectangling today was well. But first, there is another implication of nested lists:
Because dataframes (and tibbles) are built on top of lists, we can nest them to! This can sometimes come in really handy. We a dataframe contains a column that is not an atomic vector but a list (so it is a list in a list), we call it a list column:
# View(example)Use the View function, or the click in the environment panel to inspect
the nested data with a better overview.

Of course we are unlikely to build these nested tibbles by hand with the tibble function. Instead, our data usually comes from some dataset we are working with. Let’s take the familiar penguins dataset and nest it.
nest has a syntax similar to mutate, where we first specify the name
of the column to create (we call it data here),
followed by a specification of the columns to nest into that list column.
Our data column is now a list of tibbles and each individual tibble in the
list contains the data for the species of that row.
Looking into the data column’s first element, we can see that it is indeed
a regular tibble and didn’t take it personal to get stuffed into
a list column.
nested$data[[1]]To unnest the column again we use the function unnest.
Sometimes we need to be specific and use unnest_wider
or unnest_longer, but here the automatic unnest makes
the right choices already.
3.3 Exercises
3.3.1 Tidy data
For the first set of exercises I am cheating a little and take those from the (absolutely brilliant) book R for Data Science13 by the original creator of much of the tidyverse. So, for the first part, solve / answer the 4 questions found here: https://r4ds.had.co.nz/tidy-data.html#exercises-24
I do have to give another hint, because I haven’t mentioned it so far: When I introduced variables I told you that those can only contain letters, underscores and numbers and are not allowed to start with a number. However, we can use “illegal” names for variables and columns if the surround them with backticks, e.g.:
`illegal variable` <- 42
`illegal variable`[1] 42
This is how Hadley can refer to the columns named after years
in pivot_longer in exercise 1.
3.3.2 A new dataset: airlines
- Imagine for a second this whole pandemic thing is not
going on and we are planning a vacation.
Of course, we want to choose the safest airline possible.
So we download data about incident reports.
You can find it in the
./data/03/folder. - Instead of the
type_of_eventandn_eventscolumns we would like to have one column per type of event, where the values are the count for this event. - Which airlines had the least fatal accidents? What happens if we standardized these numbers to the distance theses airlines covered in the two time ranges?
- Which airlines have the best record when it comes to fatalities per fatal accident?
- Create informative visualizations and / or tables
to communicate your discoveries.
It might be beneficial to only plot e.g. the highest or lowest scoring Airlines.
One of the
slice_functions will help you there. And to make your plot more organized, you might want to have a look intofct_reorder.
3.4 Resources
- tidyr documentation
- purrr documentation
- stringr documentation for working with text and a helpful cheatsheet for the regular expressions mentioned in the video